1: What is JDBC?

Java Database Connectivity (JDBC) provides JAVA API (Application Programming Interface) to access and execute/update SQL query on any relational Database.  JDBC API uses drivers to connect with different types of databases.

Since, nearly all relational DBMSs (Database Management Systems) support SQL, and JAVA itself runs on most platforms, JDBC makes it possible to write a single database application that can run on different platforms and interact with different DBMSs.

JDBC is similar to ODBC, but specifically designed for JAVA while ODBC is language independent.

JDBC follows Bridge Design Pattern . JDBC Architecture decouples an abstraction from its implementation where JDBC API provides the abstraction and JDBC drivers provide the implementation. New drivers can be added to JDBC with changing implementation for JAVA APIs.

2: What are the advantages of JDBC 4.0?

• Auto loading of JDBC driver class. In the earlier versions we had to manually register and load drivers using class.forName().
• Connection management enhancements. new methods added to javax.sql.PooledConnection.
• SQL XML Support.

JDBC Driver

3: What are JDBC Drivers? Which JDBC Driver is fastest?

JDBC Driver is a interface that enables JAVA application to  interact with the database. The JDBC driver gives out the connection to the database and implements the protocol for transferring the query and result between client and database.

There are 4 types of JDBC drivers:

• Type 1:  JDBC-ODBC bridge driver -> Calls native code  of the locally available ODBC driver.
• Type 2:  Native-API driver (partially JAVA driver) -> calls database vendor native library on client side. This code then talk to database over network.
• Type 3:  Network Protocol driver (fully java driver)-> talks to server side middleware that then talks to database.
• Type 4:  Thin driver (fully java driver)->uses database native protocol.

Type 4 thin driver is fastest JDBC driver written in JAVA.

4: When should each of the JDBC driver type be used?

Below is a list as to when the four types of drivers can be used

• The type 1 driver is not considered a deployment-level driver and is typically used for development and testing purposes only.
• Type 2 drivers are useful in situations where a type 3 or type 4 driver is not available yet for your database.
• If your Java application is accessing multiple types of databases at the same time, type 3 is the preferred driver.
• If you are accessing one type of database, such as Oracle, Sybase, or IBM, the preferred driver type is 4.

5: What do you mean by fastest driver? Which type of JDBC driver is the fastest one?

JDBC driver performance or fastness depends on a number of issues Quality of the driver code, size of the driver code, database server and its load, Network topology, Number of times your request is translated to a different API.

Type 4: Thin driver (fully Java driver) is the fastest driver because it converts the JDBC calls into vendor specific protocol calls and it directly interacts with the database.

6: Does the JDBC-ODBC Bridge support multiple concurrent open statements per connection?

No. JDBC-ODBC Bridge can open only one Statement object per connection.

7: What is the role of Class.forName() while loading drivers?

Class.forName() creates an instance of JDBC driver and register with DriverManager.

8: JDBC-ODBC Bridge is multi-threaded or not?

No, JDBC-ODBC Bridge uses synchronized methods to serialize all the calls made to ODBC.

9: Is it possible to connect to multiple databases? Using single statement can we update or extract data from two or three or many databases?

Yes, it is possible to connect to multiple databases, at the same time, but it depends on the specific driver.

To update and extract data from the different database we can use the single statement. But we need middleware to deal with multiple databases or a single database.

10: How does JDBC handle the data types of Java and database?

The JDBC driver converts the Java data type to the appropriate JDBC type before sending it to the database. It uses a default mapping for most data types. For example, a Java int is converted to an SQL INTEGER.

JDBC Connection

11: What are JDBC Steps to connect to the Database in JAVA?

• Import packages containing the JDBC classes needed for database programming.
• Register the JDBC driver, so that you can open a communications channel with the database.
• Open a connection using the DriverManager.getConnection() method.
• Execute a query using an object of type Statement.
• Extract data from result set using the appropriate ResultSet.getXXX () method.
• Closing Connection, resultset etc.  to clean up the environment by closing all database resources relying on the JVM’s garbage collection

12: How do you register a driver?

There are 2 ways for registering the JDBC Driver:

• Class.forName() − This method dynamically loads the driver’s class file into memory, which automatically registers it. This method is preferable because it allows you to make the driver registration configurable and portable.
•  DriverManager.registerDriver() − This static method is used in case you are using a non-JDK compliant JVM, such as the one provided by Microsoft.

13: What does the Class.forName(“MyDriverClass”) do?

Class.forName(“MyDriverClass”)

• Loads the class MyDriverClass.
• Execute any static block code of MyDriverClass.
• Returns an instance of MyDriverClass.

14: When you say Class.forName() loads the driver class, does it mean it imports the driver class using import statement?

No, it doesn’t. An import statement tells the compiler which class to look for. Class.forName() instructs the Classclass to find a class-loader and load that particular Class object into the memory used by the JVM.

15: How to connect with MySQL and Oracle Database with JDBC?

MySQL Connection

try {
 Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc: mysql://localhost:3306/FacingIssuesOnIT_DB", "username", "password");     Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from user");
rs.next();
} catch (ClassNotFoundException e) {
System.err.println("ClassNotFoundException in get Connection," + e.getMessage());
}
catch (SQLException e) {
System.err.println("SQLException in getConnection," + e.getMessage());
}

Oracle Connection

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:FacingIssuesOnIT_DB", "username", "password");Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from user");
rs.next();
} catch (ClassNotFoundException e) {
System.err.println("ClassNotFoundException in get Connection," + e.getMessage());
} catch (SQLException e) {
System.err.println("SQLException in getConnection," + e.getMessage());
}

Here in above code for MySQL and Oracle Connection

Class.forName() :  creates instance of JDBC driver and register with DriverManager.

getConnection() : method will establish a connection to Database and return Connection object.

createStatement() : method create statement from Connection object to execute queries on  database.

ResultSet.next(): is pointer to next result in ResultSet.

16: What are component that constitute JDBC?

JDBC Components include connection Pools, Data Sources, and MultiPools.

17: What is Connection Pooling? How do you implement Connection Pooling?

“Connection pooling means connections will be stored in the cache and we can reuses them in  future”

Connection Pooling is a technique used for reuse of physical connections and reduced overhead for your application so that clients need not create a new connection every time to interact with the database. Connection pooling functionality minimizes expensive operations in the creation and closing of sessions. Database vendor’s help multiple clients to share a cached set of connection objects that provides access to a database.

If you use an application/web server like WebLogic, WebSphere, jBoss, Tomcat. , then your application server provides the facilities to configure for connection pooling. If you are not using an application server then components like Apache Commons DBCP Component can be used.

18: What are advantages of using a connection pool?

• It is faster.
• Connection pooling becomes easier to diagnose and analyze database connection.

19: What is JDBC DataSource and it’s advantages?

A DataSource class brings another level of abstraction than directly using a connection object. Data source can be referenced by JNDI. Data Source may point to RDBMS, file System , any DBMS etc

• A DataSource is a facility for storing data.
• DataSource can be referenced by JNDI.

For DataSource, no need to use DriverManager with the help of JNDI.  It will lookup the DataSource from Naming service server. DataSource.getConnection() method will return Connection object to the DB.

DataSource Advantages:

• Data source is dividing work among administrator and programmer/developer.
• The administrator creates a DataSource object and ties up it with JNDI registry. A programmer/ developer retrieves the DataSource object from the registry.  Then it will establish the connection with the database.

20: How to achieve JDBC Connection Pooling using JDBC DataSource and JNDI in Tomcat Server?

Connection Pooling is a technique used for reuse of physical connections and reduced overhead for your application. Connection pooling functionality minimizes expensive operations in the creation and closing of sessions. Database vendor’s help multiple clients to share a cached set of connection objects that provides access to a database. Clients need not create a new connection every time to interact with the database.

If you use an application server like WebLogic, WebSphere, jBoss, Tomcat. , then your application server provides the facilities to configure for connection pooling. If you are not using an application server then components like Apache Commons DBCP Component can be used.

21: What is Apache DBCP API?

If you use DataSource to get the Database connection, usually the code to get the connection is tightly coupled with the Driver specific DataSource implementation. Also most of the code is boiler-plate code except the choice of the DataSource implementation class.

Apache DBCP helps us in getting rid of these issues by providing DataSource implementation that works as an abstraction layer between our program and different JDBC drivers. Apache DBCP library depends on Commons Pool library, so make sure they both are in the build path.

22: What is JDBC Connection Isolation Level?

The values are defined in the class java.sql.Connection and are:

• TRANSACTION_NONE
• TRANSACTION_READ_COMMITTED
• TRANSACTION_READ_UNCOMMITTED
• TRANSACTION_REPEATABLE_READ
• TRANSACTION_SERIALIZABLE

Any given database may not support all of these levels.

TRANSACTION_READ_COMMITTED prevents dirty reads in Connection Class.

JDBC Packages and Components

23: How many packages are available in JDBC API?

Two types of packages are available in JDBC API

• java.sql
• javax.sql

24: What are JDBC API components (Interfaces and Classes)?

The java.sql package contains interfaces and classes for JDBC API.

Interfaces

• Connection
• Statement
• PreparedStatement
• CallableStatement
• ResultSet
• ResultSetMetaData
• DatabaseMetaData etc.

Classes

• DriverManager
• Blob
• Clob
• Types
• SQLException etc.

25: What is role of JDBC DriverManager Class?

The DriverManager class is an interface between user and drivers which manages the registered database drivers.

• It matches connection requests from the java application with the proper database driver using communication subprotocol.
• It can be used for transaction management.
• It provides factory method that returns the instance of Connection.
• DriverManager tracks all the activity between a database and the appropriate driver.

26: What does JDBC Connection Interface?

The Connection Interface maintains a session with the Database. It can be used for transaction management.It provides factory method that returns the instance of Statement, PreparedStatement, CallableStatement and DatabaseMetaData.

27: Why should we close database connections in Java?

We must close the ResultSet, the Statement and the Connection. If the connection is coming from a pool, on closure, the connection is sent back to the pool for reuse. We are doing this in the finally{} block, because if any exception occurs, then we still get the chance to close this.

JDBC Statements

28: What are JDBC types of Statements?

The Statement acts just like a vehicle via which SQL commands are sent. – By the connection objects, we create the Statement kind of objects.

There are 3 JDBC statements :

1. Statement : encapsulates an SQL statement which is passed to the database to be parsed, compiled, planned and executed.
2. PreparedStatement : Used when we want to execute SQL statement repeatedly. Input parameters  set dynamically at runtime.
3. CallableStatement: Used when need to execute stored procedure or function.

29: What is difference between Statement, PreparedStatement and CallableStatement Interface?

In case of Statement, query is complied each time whereas in case of PreparedStatement, query is complied only once. So performance of PreparedStatement is better than Statement.

30: What are benefits and limitation of PreparedStatement? Why Prepared Statement are faster?

PreparedStatement Benefits

PreparedStatement is  used to execute same SQL statements repeatedly. The prepared statement is compiled only once and execute any number of times

Prepared statement execution is faster than direct execution because the statement is compiled only once. Prepared statements & JDBC driver are connected with each other during execution, and there are no connection overheads.

PreparedStatement Limitations

In order to prevent SQL Injection attacks in Java, PreparedStatement doesn’t allow multiple values for one placeholder (?) who makes it tricky to execute SQL query with IN clause.

31: What is difference between execute, execiteQuery and executeUpdate?

boolean execute(): it can be used for any kind of SQL Query. It’s return type is Boolean.

ResultSet executeQuery() : it can be used for select query for reading content of the database. It’s return type is ResultSet object.

int executeUpdate(): it can be used to change/update table for altering the databses. Generally DROP TABLE or DATABASE, INSERT into TABLE, UPDATE TABLE, DELETE from TABLE statements will be used in this. The output will be in the form of int which denotes the number of rows affected by the query.

32: What is Stored Procedure and How can we execute stored procedure and functions in JDBC?

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task. For example operations on an employee database (joining, termination, promotion, lookup) could be coded as stored procedures executed by application code.

By using CallableStatement interface, we can execute database stored procedure and function.

Connection conn = null;
CallableStatement callStmt = conn.prepareCall("{call myTestStoreProcedure(?, ?)}");
callStmt.setString(1, "FacingIssuesOnIT");

33: Tell me difference between setMaxRows(int) and SetFetchSize(int)?

34: Is there a practical limit for the number of SQL statements that can be added to an instance of a Statement object?

The specification makes no mention of any size limitation for Statement.addBatch(), this is dependent, on the driver.

ResultSet and RowSet

35: What does JDBC ResultSet Interface and  ResultSet Types?

The java.sql.ResultSet interface means the result of a SQL query. ResultSet objects holds data retrieve from database. It acts as iterator to allow move through data. Initially it points to before the first row. It can be used to change the cursor pointer and get information from the database.

There are three types of ResultSet is available. If we do not declare any ResultSet that means we are calling default ResultSet TYPE_FORWARD_ONLY.

1.  TYPE_FORWARD_ONLY: cursor can move only forward.
2. TYPE_SCROLL_INSENSITIVE: cursor can move forward and backward but not sensitive to changes made by others to the database that occur after the result set was created.
3. TYPE_SCROLL_SENSITIVE: cursor can move forward and backward, but it is sensitive  to changes made by others to the database that occur after the result set was created.

36: How many ways that we can view a result set?

There are 2 ways to view ResultSet:

1. column
2. column index.

Example:

getInt(String columnName).

getInt(int columnIndex).

37: Result Set’s index Starts with 0 or 1? What happens if we call resultSet.getInt(0)?

Result Set’s index starts with 1. It throws InvalidColumnIndexException in JDBC because index for getXXX() or setXXX() in JDBC starts with 1.

38: Can I get a null ResultSet?

No, we cannot get null Resultset. ResultSet.next() can return null if the next record does not contain a row.

39: How many ways can you update a result set?

Following methods helps you to update result set:

• updateRow()
• deleteRow()
• refreshRow()
• cancelRowUpdates()
• insertRow()

40: How to set the attribute Concurrency in ResultSet?

The ResultSet concurrency determines whether the ResultSet can be updated, or only read. A ResultSet can have one of two concurrency levels:

• ResultSet.CONCUR_READ_ONLY – means that the ResultSet can only be read.
• ResultSet.CONCUR_UPDATABLE − means that the ResultSet can be both read and updated.

41: What is JDBC Rowset? What are different types of Rowset?

A JDBC RowSet object holds tabular data in a way that makes it more flexible and easier to use than a result set. A RowSet objects are JavaBeans components.

There are two types of RowSet:

• Connected: A connected RowSet Object is permanent in nature. It doesn’t terminate until the application is terminated.
• Disconnected :A disconnected RowSet object is ad-hoc in nature. Whenever it requires retrieving data from the database, it establishes the connection and closes it upon finishing the required task. The data that is modified during disconnected state is updated after the connection is re-established.

42: What is difference between ResultSet and RowSet?

RowSet extends ResultSet and add support for JDBC API to Java bean component model. Main difference of ResultSet and RowSet is RowSet being connected and disconnected.

In a ResultSet handle connection to a DB, we cannot make Result as a serialized object Because of above issue, we cannot pass  Resultset across the network.

RowSet extends the ResultSet interface, so it holds all methods from ResultSet. RowSet is serialized.

So, we can pass Rowset from one class to another class because it has no connection with the database.

43: What is the reason why we need a JDBC RowSet like the wrapper around ResultSet?

We can use ResultSet object as a JavaBeans component.

• A JDBC RowSet also can be used as a JavaBeans component. That’s why it can be created and configured at design or compile time and executed at run time.
• All JDBC RowSet objects are scrollable and updatable.

44: What is the difference between client and server database cursors?

Server side cursor means data & results are saved on the server. Only when requested data is sent to the client.

Client side cursor means all data sent to the client location.

45: How cursor works in scrollable result set?

There are several methods in the ResultSet interface that involve moving the cursor, like beforeFirst(), afterLast(), first(), last(), absolute(int row), relative(int row), previous(), next(), getRow(), moveToInsertRow(), moveToCurrentRow().

JDBC Meta Data

46: What do you mean by Metadata and why we are using it?

The meta data provides comprehensive information about the database as a whole. The implementation for these interfaces is implemented by database driver vendors to let users know the capabilities of a Database.

Metadata means data or information about other data. We use metadata to get database product version, driver name, the total number of tables and views.

47: What are type of Meta Data Interfaces in JDBC and uses?

ResultSetMetaData: interface returns the information of table such as total number of columns, column name, column type etc. java.sql.ResultSetMetaData extends java.sql.Wrapper.

ResultSetMetaData important methods :

• getColumnCount() – To find out total number of columns in table.
• getColumnName() –  Display table’s column type.
• getColumnTypeName() –  Display table’s column type

DatabaseMetaData: interface returns the information of the database such as username, driver name, driver version, number of tables, number of views etc. java.sql.DatabaseMetaData extends java.sql.Wrapper.

DatabaseMetaData important methods:

• getDriverName() – Returns driver name.
• getDriverVersion() – returns driver version.
• getDatabaseProductName() – returns database name
• getDatabaseProductVersion() – returns database version
• getUserName() – returns username used to connect to database.
• getURL() – returns URL used to connect to database.
• getDatabaseMinorVersion() – returns database’s minor/initial version.
• getDatabaseMajorVersion() – returns current database version.

Best Example for JDBC Meta Data

How to create HTML dynamic table in JSP from ResultSet with unknown columns ?

48: How can I determine whether a Statement and its ResultSet will be closed on a commit or rollback?

Use the DatabaseMetaData methods supportsOpenStatementsAcrossCommit() and supportsOpenStatementsAcrossRollback() to check.

JDBC Transaction Management

49: What is a transaction?

A transaction is a logical unit of work. To complete a logical unit of work, several actions may need to be taken against a database. Transactions are used to provide data integrity, correct application semantics, and a consistent view of data during concurrent access.

50: How will you insert multiple rows into a database in a single transaction?

Follow below steps:

• turn off the implicit commit

Connection.setAutoCommit(false);

• your insert/update/delete goes here
• Commit your statements

Connection.Commit()

51: What is JDBC Transaction Management and Types? Why do we need it?

A transaction is a group of operation used to performed one task if all operations in the group are success then the task is finished and the transaction is successfully completed. If any one operation in the group is failed then the task is failed and the transaction is failed.

Properties of Transaction Management (ACID)

Atomicity: Atomicity of a transaction is nothing but in a transaction either all operations can be done or all operation can be undone, but some operations are done and some operation are undone should not occur.

Consistency: Consistency means, after a transaction completed with successful, the data in the data store should be a reliable data this reliable data is also called as consistent data.

Isolation: Isolation means, if two transaction are going on same data then one transaction will not disturb another transaction.

Durability: Durability means, after a transaction is completed the data in the data store will be permanent until another transaction is going to be performed on that data.

Advantage of Transaction Management

fast performance It makes the performance fast because database is hit at the time of commit.

Types of Transaction

• Local Transaction
• Distributed or global transaction

Local Transaction: A local transaction means, all operation in a transaction are executed against one database.
For example; If transfer money from first account to second account belongs to same bank then transaction is local transaction.

Global Transaction: A global transaction means, all operations in a transaction are executed against multiple database.
For Example; If transfer money from first account to second account belongs to different banks then the transaction is a global transaction.

52: Which Interface is responsible for transaction management?

The Connection interface provides methods for transaction management such as commit(), rollback() etc.

53: What does setAutoCommit do? Why would you use setAutoCommit(false) in JDBC?

When a connection is created, it is in auto-commit mode. This means that each individual SQL statement is treated as a transaction and will be automatically committed right after it is executed. By setting auto-commit to false no SQL statements will be committed until you explicitly call the commit method.

If you want to turn Off the Auto Commit then set connection.setAutoCommit(false). There several benefits of doing it:

• To increase performance.
• To maintain the integrity of business processes.
• To use distributed transactions.

54: How to rollback a JDBC transaction?

Connection.rollback();

55: What is JDBC savepoint? How to use it? Give an example?

A savepoint marks a point that the current transaction can roll back to. Instead of rolling all of its changes back, it can choose to roll back only some of them. For example, suppose you

• start a transaction.
• insert 20 rows into a table.
• set a savepoint.
• insert another 5 rows.
• rollback to the savepoint.
• commit the transaction.

After doing this, the table will contain the first 20 rows you inserted. The other 5 rows will have been deleted by the rollback. A savepoint is just a marker that the current transaction can roll back to.

56: What are the standard Transaction isolation levels defined by JDBC?

The standard isolation levels are:

• TRANSACTION_NONE
• TRANSACTION_READ_COMMITTED
• TRANSACTION_READ_UNCOMMITTED
• TRANSACTION_REPEATABLE_READ
• TRANSACTION_SERIALIZABLE

JDBC Batch Processing

57: What is batch processing and how to perform batch query execution in JDBC?

Batch processing technique in JDBC, allows you to group related SQL statements into a batch and submit them with one call to the database. It makes the performance fast

Batch updates can be used only for insert, update and delete but not for select query.

Steps to do Batch Processing in JDBC:

• Create a Statement or PrepareStatement object using either createStatement() or prepareStatement() methods respectively with place holder.
• Set auto-commit to false using setAutoCommit().
• Add as many as SQL statements you like into batch using addBatch() method on created statement object.
• Execute all the SQL statements using executeBatch() method on created statement object.
• Finally, commit all the changes using commit() method.

JDBC Object and File Handling

58: How to store and retrieve images from database?

Images in the database using the BLOB datatype wherein the image stored as a byte stream. Below code is showing how to insert the image into DB.

59: What is CLOB and BLOB datatypes in JDBC? What is the use of blob, clob datatypes in JDBC?

These are used to store large amount of data into database like images, movie etc which are extremely large in size.

For more detail check Difference between CLOB and BLOB in Data Type Questions.

60: How to Connect to an Excel Spreadsheet using JDBC in Java?

Follow the steps below

First setup the new ODBC datasource. Goto Administrative Tools−>Data Sources (ODBC)−>System DSN tab−>Add−>Driver do Microsoft Excel(*.xls)−>Finish. Now give the Data Source Name (SampleExcel) & Description. Next, click Select Workbook and point to your excel sheet.

In the code make to following code additions

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

Connection conn = DriverManager.getConnection("jdbcodbcSampleExcel","","");

stmt = conn.createStatement();

sql = "select * from [Sheet1$]";

rs=stmt.executeQuery(sql);

Where Sheet1 is the excel sheet name.

61: How to insert an image or raw data into database?

try {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:FacingIssuesOnITDB","username","yourpassword");
PreparedStatement prs = con.prepareCall("insert into emp_pers_profile values (?,?)");
prs.setInt(1, 5453);
InputStream inputstrm = new FileInputStream(new File("emp_img.jpg"));
prs.setBinaryStream(2, inputstrm);
int count = prs.executeUpdate();
} catch (ClassNotFoundException e) {
System.err.println("ClassNotFoundException in get Connection," + e.getMessage());
}
catch (SQLException e) {
System.err.println("SQLException in getConnection," + e.getMessage());
}
catch (IOException e) {
System.err.println("IOException to get Image File," + e.getMessage());
}

JDBC Exception Handling

62: What are common JDBC Exceptions?

There are four types of exceptions in JDBC.

1. batchUpdate Exception
2. Data Truncation
3. SQL Exception
4. SQL Warning

To know more on JDBC Issues/Exception Solution follow below link:

JDBC Issues and Solutions

63: What are database warnings in JDBC and how can we handle database warnings in JDBC?

SQLWarning objects are a subclass of SQLException that deal with database access warnings. Warnings do not stop the execution of an application, as exceptions do. They simply alert the user that something did not happen as planned. A warning can be reported on a Connection object, a Statement object (including PreparedStatement and CallableStatement objects), or a ResultSet object. Each of these classes has a getWarnings() method.

64: When will get the message “No Suitable Driver”?

“No suitable driver” occurs when we are calling DriverManager.getConnection method, It may occur because of following reasons:

• Unable to load exact JDBC drivers before calling the getConnection method.
• It may be invalid or wrong JDBC URL, one that is not recognized by JDBC driver.
• This error can occur if one or more the shared libraries needed by the bridge cannot be loaded.

Data Types

65: What is CLOB and BLOB datatypes in JDBC?

These are used to store large amount of data into database like images, movie etc which are extremely large in size.

66: What Does It Mean To “materialize” Data?

This term generally refers to Array, Blob and Clob data which is referred to in the database via SQL locators “Materializing” the data means to return the actual data pointed to by the Locator.

• For Arrays, use the various forms of getArray() and getResultSet().
• For Blobs, use getBinaryStream() or getBytes(long pos, int length).
• For Clobs, use getAsciiStream() or getCharacterStream().

67: Difference between SQL Date and java.util.Date in Java?

Main difference between SQL data i.e. java.sql.Date and util date i.e. java.util.Date is that SQL Date only contains date part and not time part but util date contains both date and time part.

68: What is difference between java.sql.Time and java.sql.TimeStamp in Java?

Main difference is that java.sql.Time class doesn’t contain any date information on it while java.sql.TimeStamp contains date information.

General JDBC Questions

69: What are JDBC Best Practices?

1. Use Connection Pooling : Connection pooling is the process where we maintain cache of database connections so that reused whenever request comes to connect with database. Connection Pooling reduces databse hits and improves application performance significantly. Database hit is a very costly operation and as much as possible you must try to avoid it.
2. PreparedStatement is used for executing a precompiled SQL statement. java.sql.PreparedStatement suitable for executing DML commands –  SELECT, INSERT, UPDATE and DELETE. PreparedStatement is faster as compared to Statement because it is used for executing precompiled SQL statement. Hence,  same SQL query can be executed repeatedly in PreparedStatement.
3. Statement is used for executing a static SQL statement. java.sql.Statement is suitable for executing DDL commands – CREATE, drop, alter and truncate.
4. Avoid SQL injection:PreparedStatement prevents SQL injection, because text for all the parameter values is escaped.
5. Use Batch Statement: Batch statement sends multiple requests from java to database in one just one call while without batch statements multiple requests will be in sent in multiple (one by one) calls to the database.
6. Use Column Name for Extracting Data from result set: In case database table column sequence and count change will not any impact on code.
7. Specify Column Name in Select Query: Rather than using queries like “select * from EMPLOYEE”, you must must specify column name which you want to fetch from database like this  “select ID, NAME from EMPLOYEE”.
8. Use Stored procedure and functions: Write as much business logic as much as possible in Stored Procedure or Functions as compared to writing it down in java class. Because that reduces databse hits and improves application performance significantly. You must remember that database hit is a very costly operation and you must try to avoid it as much as possible.
9. Use Transaction Management : connection.setAutoCommit(false), connection.commit() and connection.rollback(). We can set autocommit mode of connection to false using connection.setAutoCommit(false) and then accordingly use connection.commit() or connection.rollback().If any transaction fails in between then rollback the transaction by calling con.rollback(), commit the transaction by using con.commit() only if it went successful.
10. Close Resources: You must ensure that you close all the JDBC Statement, PreparedStatement, CallableStatement , ResultSet and Connections in java to avoid ora-01000 maximum open cursors exceeded java.sql.SQLException in java. You must always close all the above mentioned objects in finally block in java because finally block is always executed irrespective of exception is thrown or not by java code.

70: Tell me about special characters?

A special character is preceded by an escape character. Example –SELECT NAME FROM TABLE WHERE NAME LIKE ‘_%’ {escape ‘\’}

71: What is JDBC SQL escape syntax?

The escape syntax gives you the flexibility to use database specific features unavailable to you by using standard JDBC methods and properties.

The general SQL escape syntax format is as follows:

{keyword ‘parameters’}.

JDBC defines escape sequences that contain the standard syntax for the following language features

• Date, time, and timestamp literals (d, t, ts Keywords).
• Scalar functions such as numeric, string, and data type conversion functions(fn Keyword).
• Outer joins(oj Keyword)
• Escape characters for wildcards used in LIKE clauses(escape Keyword).
• Procedure calls(call Keyword).

72: What is “dirty read” in JDBC? Which isolation level prevents dirty read?

DIRTY READS: Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction, thus getting you incorrect or wrong data.

This happens at READ UNCOMMITTED transaction isolation level, the lowest level. Here transactions running do not issue SHARED locks to prevent other transactions from modifying data read by the current transaction. This also do not prevent from reading rows that have been modified but not yet committed by other transactions.

To prevent Dirty Reads, TRANSACTION_READ_COMMITTED or SNAPSHOT isolation level should be use

Dirty read means “read the value which may be correct or may not be correct.”

73: What is 2 phase commit?

Two phase commit is used in distributed transaction process. If any transaction is executing and it will affect multiple databases. Two phase commits will be used to make all databases synchronized with each other.

74: What are the different types of locking in JDBC?

Two types of locking are available in JDBC by which we can handle more than one user.

If two users are viewing the same record, then no locking is done. If one user is updating a record and the second user is also updating the same record.  At that time, we are going to use locking.

1.  Optimistic Locking: it will lock the record only when we are going to “update.”
2. Pessimistic Locking: it will lock the record from the “select” to view, update and commit time.

75: What do you understand by DDL and DML statement?

• DML statements are SQL statements that manipulate data. DML stands for Data Manipulation Language. The SQL statements that are in the DML class are INSERT, UPDATE and DELETE. Some people also lump the SELECT statement in the DML classification.
• Data Definition Languages (DDL) are used to define the database structure. Any CREATE, DROP and ALTER commands are examples of DDL SQL statements.

76: What is phantom read and which isolation level prevents it?

Data getting changed in current transaction by other transactions is called Phantom Reads. New rows can be added by other transactions, so you get different number of rows by firing same query in current transaction.

In REPEATABLE READ isolation levels Shared locks are acquired. This prevents data modification when other transaction is reading the rows and also prevents data read when other transaction are modifying the rows. But this does not stop INSERT operation which can add records to a table getting modified or read on another transaction. This leads to PHANTOM reads.

PHANTOM reads can be prevented by using SERIALIZABLE isolation level, the highest level. This level acquires RANGE locks thus preventing READ, Modification and INSERT operation on other transaction until the first transaction gets completed.